﻿using BusinessRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BusinessRule.Organization;
using BusinessEntity;
using BusinessEntity.xmglQuerryEntity;
using learun.util;
using System.Dynamic;

namespace BusinessRule
{
    public class CProManageRule
    {

        #region 获取项目列表  过滤条件（项目名称、项目分类、代建单位、资金来源）

        //计划可用余额
        public static async Task<dynamic> GetJHkyye(string xmid, string jhid)
        {
            string strsql = @"select *,xmztz-yyje jhkyje from (
select id, xmztz+isnull((select SUM(bgsdje) from CProTZJHbgS where
  jhndid  in (select id from CProTZJHND where jhid = CProTZJH.id ) and status = '2'),0) xmztz,(
  select SUM(totalmoney)totalmoney from CProManage
  where id in (select xmid from CProManageTZXM where txmid = CProTZJH.id) and status in ('0','1','2')
  and id <> '" + xmid + "')yyje from CProTZJH where id = '"+ jhid + "')t ";

            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);
        }

        #region  项目
        //public static async Task<(DataTable, int)> GetXMList(CProManageQuerry querry, string uid)
        //{
        //    string str = "";
        //    if (!(string.IsNullOrEmpty(querry.jsdw)))
        //    {
        //        str = str + " and BuildUnit in ('" + querry.jsdw + "')";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.xmmc)))
        //    {
        //        str = str + " and xmmc like '%" + querry.xmmc + "%'";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.xmfl)))
        //    {
        //        str = str + " and xmflname = '" + querry.xmfl + "'";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.zjly)))
        //    {
        //        str = str + " and zjly = '" + querry.zjly + "'";
        //    }
        //    string sql = "select * from View_ProLists where departid in " + await UserRule.GetUserDepid(uid) + str.Replace("''", "'");
        //    bool isAsc = true;
        //    if (querry.sort != "Asc" && querry.sort != "asc")
        //    {
        //        isAsc = false;
        //    }
        //    if (string.IsNullOrEmpty(querry.column))
        //    {
        //        querry.column = "id";
        //    }
        //    return await new RepositoryFactory().BaseRepository().FindTable(sql, null, querry.column, isAsc, querry.size, querry.current);

        //}

        //项目首页报表
        
        public static async Task<(IEnumerable<dynamic>, int)> GetXMIndexList(CProManageQuerry querry, string strdepids)
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmfl)))
            {
                str = str + " and xmflname = '" + querry.xmfl + "'";
            }
            if (!(string.IsNullOrEmpty(querry.xmflname)))
            {
                str = str + " and xmflname = '" + querry.xmflname + "'";
            }
            if (!(string.IsNullOrEmpty(querry.zjly)))
            {
                str = str + " and zjly = '" + querry.zjly + "'";
            }
            if (!(string.IsNullOrEmpty(querry.year)))
            {
                str = str + " and xmnf = '" + querry.year + "'";
            }
            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }

            str += " and id not in (select xmid from CProGD)";

            string strsql = "select * from View_ProLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'") + "";
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }

        //项目首页报表
        public static async Task<(IEnumerable<dynamic>, int)> GetXMIndexListHasNoStart(CProManageQuerry querry, string strdepids)
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmfl)))
            {
                str = str + " and xmflname = '" + querry.xmfl + "'";
            }
            if (!(string.IsNullOrEmpty(querry.zjly)))
            {
                str = str + " and zjly = '" + querry.zjly + "'";
            }
            if (!(string.IsNullOrEmpty(querry.year)))
            {
                str = str + " and xmnf = '" + querry.year + "'";
            }
            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }

            str += " and id not in (select xmid from CProGD)";

            //计划开工
            str += " and id not in (select xmid from CProManageJHKG)";

            string strsql = "select * from View_ProLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }

        #endregion

        public static async Task<IEnumerable<dynamic>> Gettzjhjzbyyear(string xmid, string keyValue)
        {
            string strsql = @"select jhid,id,tzxmmc,jhnd,bnjhtzzje,ISNULL(bnsjzfje,0)bnsjzfje from View_ProTZJHJZLists
where bnjhtzzje - ISNULL(bnsjzfje,0) >0 and jhnd = '" + keyValue + @"' and status = '2' 
order by jhnd desc,id desc";
            if (!string.IsNullOrEmpty(xmid))
            {
                strsql = @"select jhid,id,tzxmmc,jhnd,bnjhtzzje,ISNULL(bnsjzfje,0)bnsjzfje from View_ProTZJHJZLists
where bnjhtzzje -ISNULL(bnsjzfje, 0) > 0 and jhnd = '" + keyValue + @"' and status = '2'
and id not in (select txmid from CProManageTZXM where xmid = '" + xmid + @"')
order by jhnd desc, id desc";
            }
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        public static async Task<IEnumerable<dynamic>> Gettzjhjz()
        {
            string strsql = @"select jhid,id,tzxmmc,jhnd,bnjhtzzje,ISNULL(bnsjzfje,0)bnsjzfje from View_ProTZJHJZLists
where bnjhtzzje -ISNULL(bnsjzfje, 0) > 0 and status = '2'
order by jhnd desc, id desc";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        public static async Task<IEnumerable<dynamic>> GettzjhjzByxmid(string keyValue)
        {
            string sql = @"select id,jhid,tzxmmc,jhnd,bnjhtzzje from View_ProTZJHJZLists where id in (
select txmid from CProManageTZXM
where xmid = '" + keyValue + "')";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        public static async Task<IEnumerable<dynamic>> GettzjhByxmid(string keyValue)
        {
            string sql = @"select id,tzxmmc from View_ProTZJHLists where id in (
select txmid from CProManageTZXM
where xmid = '" + keyValue + "')";

            sql = "select txmid from CProManageTZXM where xmid = '" + keyValue + "'";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //现场项目信息关联单位信息
        public static async Task<dynamic> GetXMByID2(string keyValue)
        {
            string sql = @"
select* from(
select* from View_ProLists
where id = '" + keyValue + @"'
)t2
left join(
select '" + keyValue + @"' xmid, (
select dwmc + ',' from dbo.CProConFuZeRen
where dwlb = '施工单位' and htid in (select id from CProContract where dxid in (
select id from CProManageLX where xmid = '" + keyValue + @"'
))
for xml path('')
) yf,(
select dwmc + ',' from dbo.CProConFuZeRen
where dwlb = '监理单位' and htid in (select id from CProContract where dxid in (
select id from CProManageLX where xmid = '" + keyValue + @"'
))
for xml path('')
) jlht,(
select dwmc + ',' from dbo.CProConFuZeRen
where dwlb = '设计单位' and htid in (select id from CProContract where dxid in (
select id from CProManageLX where xmid = '" + keyValue + @"'
))
for xml path('')
) sjht,(
select dwmc + ',' from dbo.CProConFuZeRen
where dwlb = '勘察单位' and htid in (select id from CProContract where dxid in (
select id from CProManageLX where xmid = '" + keyValue + @"'
))
for xml path('')
) kcht
)t on t2.id = t.xmid";
            return await new RepositoryFactory().BaseRepository().FindEntity(sql, null);
        }

        //查视图
        public static async Task<dynamic> GetXMByID(string keyValue)
        {
            string sql = "select * from View_ProLists where id = '" + keyValue + "'";
            return await new RepositoryFactory().BaseRepository().FindEntity(sql, null);
        }
        //查表
        public static async Task<IEnumerable<dynamic>> GetXMByIDTb(string keyValue)
        {
            string sql = "select * from CProManage where id = '" + keyValue + "'";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //树状菜单
        public static async Task<DataTable> GetFuncTreeByUid(string uid)
        {
            DataTable dts = new DataTable();
            dts.Columns.Add("Code");
            dts.Columns.Add("Name");
            dts.Columns.Add("status");
            //一级菜单
            string sql = "select catalogid,catalogname from View_TSUserFunction where UserID = '" + uid + "' and catalogid IS not null group by catalogid,catalogname order by charindex(catalogid,'c2c4c1c3')";
            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);
            for (int num = 0; num < table.Rows.Count; num++)
            {
                DataRow row = dts.NewRow();
                row["Code"] = table.Rows[num]["catalogid"].ToString();
                row["Name"] = table.Rows[num]["catalogname"].ToString();
                row["status"] = "1";
                dts.Rows.Add(row);
                //二级菜单
                string Caid = table.Rows[num]["catalogid"].ToString();
                string sql2 = "select extid,extname from View_TSUserFunction where UserID = '" + uid + "' and catalogid = '" + Caid + "' and ExtName != '报规报批' group by extid,extname order by convert(int, extid) asc";
                DataTable table2 = await new RepositoryFactory().BaseRepository().FindTable(sql2, null);
                for (int i = 0; i < table2.Rows.Count; i++)
                {
                    DataRow row2 = dts.NewRow();
                    row2["Code"] = table2.Rows[i]["extid"].ToString();
                    row2["Name"] = table2.Rows[i]["extname"].ToString();
                    row2["status"] = "2";
                    dts.Rows.Add(row2);
                    //三级菜单
                    string Extid = table2.Rows[i]["extid"].ToString();
                    string sql3 = "select * from View_TSUserFunction where UserID = '" + uid + "' and ExtId ='" + Extid + "' order by cast (functionorder as int)  asc";
                    DataTable table3 = await new RepositoryFactory().BaseRepository().FindTable(sql3, null);
                    for (int j = 0; j < table3.Rows.Count; j++)
                    {
                        DataRow row3 = dts.NewRow();
                        row3["Code"] = table3.Rows[j]["functioncode"].ToString();
                        row3["Name"] = table3.Rows[j]["FunctionName"].ToString();
                        row3["status"] = "3";
                        dts.Rows.Add(row3);

                    }
                }
            }
            return dts;
        }
        #endregion

        #region 项目分类、代建单位、资金来源
        public static async Task<IEnumerable<T>> GetDicByType<T>(string type) where T : class
        {

            string sql = "select * from BDicInfo where type = '" + type + "'";

            return await new RepositoryFactory().BaseRepository().FindList<T>(sql, null);

        }


        public static async Task<string> Getks(string userid)
        {
            string str2 = "(";
            string sql = "select ksid from view_userinfos where userid ='" + userid + "'";
            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);

            for (int i = 0; i < table.Rows.Count; i++)
            {
                string[] strArray = table.Rows[i]["ksid"].ToString().Split(new char[] { ',' });
                for (int j = 0; j < strArray.Length; j++)
                {
                    str2 = str2 + "'" + strArray[j].ToString() + "',";
                }
                str2 = str2.TrimEnd(new char[] { ',' });
            }
            return (str2 + ")");
        }

        public static string GetksPro(string userid)
        {
            return Getks(userid).GetAwaiter().GetResult().Replace("'", "''");
        }

        public static async Task<IEnumerable<dynamic>> GetJsdw(string strdepids)// where T : class
        {
            //status = '1' and  
            string sql = "select id,bmmc,frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh,pid from BGlbmInfo where isjsdw = '1' and qxid in " + strdepids;

            return await new RepositoryFactory().BaseRepository().FindList<dynamic>(sql, null);

        }

        #endregion

        #region 提交数据
        public async static Task<bool> GetTzxmjeVid(string tzxmid, string xmid, decimal je)
        {
            //先删除关联表
            string strsql = @"select id,jhid,jhnd,bnjhtzje,bgje,bnjhtzzje,bnjhapje,jzje,
isnull((
select SUM(fpje) from CProManageTZXM where txmid = View_ProTZJHJZLists.id
and xmid <> '" + xmid + @"'
),0) + " + je + " fpzje from View_ProTZJHJZLists where id = '" + tzxmid + "'";
            decimal bnjhtzzje = 0;
            decimal fpzje = 0;
            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql, null);// TranstionRule.GetExist(strsql);
            for (int i = 0; i < dts.Rows.Count; i++)
            {
                bnjhtzzje = CommonHelper.ParseDecimal(dts.Rows[i]["bnjhtzzje"].ToString());
                fpzje = CommonHelper.ParseDecimal(dts.Rows[i]["fpzje"].ToString());
            }
            if (bnjhtzzje < fpzje)
            {
                return true;
            }
            else
                return false;
        }

        //新增时插入概算、标段、前期节点
        //List<CProManageTZXM>
        public static async Task<bool> InsertCProManage(CProManage cpro, List<CProManageTZXM> tzxmids, string uid, (string, string) apiinfo)
        {
            //CProManage count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProManage>(cpro.ID);

            //固定单体
            //string sql = "select * from bdicinfo where type = '固定单体' order by torder asc ";
            //DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);

            //protect_section_type
            //获取字典protect_section_type 的值
            string strdxlx = ConfigHelper.GetAppSettings("ServerOp:dxlx");
            List<JavaApi.DataItemDIC> list = await JavaApi.GetDicJavaAPI(strdxlx, apiinfo);

            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                //string strid = CommonHelper.GetUniqueId().ToString();
                //cpro.ID = strid;
                if (string.IsNullOrEmpty(cpro.process))
                {
                    cpro.process = "0";
                }
                //if (string.IsNullOrEmpty(cpro.status))
                //{
                //    cpro.status = "2";
                //}

                //cpro.status = BaseEditPower.GetSPEnumValue("待提交");
                //cpro.CreateUser = uid;

                await irp.Insert(cpro);

                //如果没有投资计划明细就不用加了
                if (null != tzxmids)
                {
                    //投资项目关联
                    for (int i = 0; i < tzxmids.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(tzxmids[i].txmid))
                        {
                            CProManageTZXM tzxm = new CProManageTZXM();
                            tzxm.id = CommonHelper.GetUniqueId().ToString();
                            tzxm.xmid = cpro.id;//项目id
                            tzxm.txmid = tzxmids[i].txmid.ToString();//投资项目id
                            tzxm.fpje = tzxmids[i].fpje;//分配金额
                            tzxm.createdate = DateTime.Now;
                            tzxm.createuser = uid;

                            await irp.Insert(tzxm);
                        }
                    }
                }

                for (int i = 0; i < list.Count; i++)
                {
                    CProManageLX elx = new CProManageLX
                    {
                        id = CommonHelper.GetUniqueId().ToString(),
                        xmid = cpro.id,
                        pid = "0",
                        dxmc = list[i].value.ToString(),
                        status = "2",
                        createdate = DateTime.Now,
                        createuser = ""
                    };
                    await irp.Insert(elx);
                }

                //for (int num = 0; num < table.Rows.Count; num++)
                //{
                //    CProManageLX elx = new CProManageLX
                //    {
                //        id = CommonHelper.GetUniqueId().ToString(),
                //        xmid = cpro.ID,
                //        pid = "0",
                //        dxmc = table.Rows[num]["tname"].ToString(),
                //        status = "2",
                //        createdate = DateTime.Now,
                //        createuser = ""
                //    };
                //    await irp.Insert(elx);

                //}
                //for (int num = 0; num < table.Rows.Count; num++)
                //{
                //    CProManageGS gs = new CProManageGS
                //    {
                //        id = CommonHelper.GetUniqueId().ToString(),
                //        xmid = cpro.ID,
                //        pid = "0",
                //        dxmc = table.Rows[num]["tname"].ToString(),
                //        status = "2",
                //        createdate = DateTime.Now,
                //        createuser = ""
                //    };
                //    await irp.Insert(gs);
                //}

                return true;

            });
        }

        public static async Task<bool> UpdateCProManage(CProManage cpro, List<CProManageTZXM> tzxmids, string uid)
        {

            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                await irp.Update(cpro);

                if (null != tzxmids && tzxmids.Count > 0)
                {
                    //先删除关联的表
                    await irp.DeleteAny<CProManageTZXM>(new { xmid = cpro.id });
                    //投资项目关联
                    for (int i = 0; i < tzxmids.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(tzxmids[i].txmid))
                        {
                            CProManageTZXM tzxm = new CProManageTZXM();
                            tzxm.id = CommonHelper.GetUniqueId().ToString();
                            tzxm.xmid = cpro.id;
                            tzxm.txmid = tzxmids[i].txmid;
                            tzxm.fpje = tzxmids[i].fpje;

                            tzxm.createdate = DateTime.Now;
                            tzxm.createuser = uid;

                            await irp.Insert(tzxm);
                        }
                    }
                }

                return true;

            });
            //await new RepositoryFactory().BaseRepository().Update(cpro);
        }

        //删除单条
        public static async Task DeleteCProManage(string keyValue)
        {
            CProManage count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProManage>(keyValue);
            if (count is null)
            {
                throw new Exception("没有相关数据");
            }
            if (!await BaseEditPower.GetEditPower(keyValue, BaseEditPower.TableName.项目))
            {
                throw new Exception("请确认数据权限");
            }
            else
            {
                await new RepositoryFactory().BaseRepository().ExecuteProc("deleteproject", new { proid = keyValue });
                //await new RepositoryFactory().BaseRepository().ExecuteSql("exec deleteproject '" + keyValue + "'");
            }
        }
        //删除多条
        public static async Task DeleteCProManageMulti(Root ids)
        {
            foreach (BaseDeleteMulti idm in ids.ids)
            {
                if (!await BaseEditPower.GetEditPower(idm.id, BaseEditPower.TableName.项目))
                {
                    throw new Exception("请确认数据权限");
                }
            }


            await TranstionRule.GetRunActionTransAsync(async (iRep) =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await new RepositoryFactory().BaseRepository().ExecuteProc("deleteproject", new { proid = idm.id });
                }
                return true;
            });


        }
        //修改删除权限
        public static async Task<bool> GetEditPowerAsync(string xmid)
        {
            string sql = "select * from View_ProLists where id = '" + xmid + "'";
            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);
            for (int i = 0; i < table.Rows.Count; i++)
            {
                string str2 = table.Rows[i]["status"].ToString();
                string str3 = table.Rows[i]["nextlevels"].ToString();
                string str4 = table.Rows[i]["sprcc"].ToString();
                if ((str2 == "1") || ((str2 == "2") && (str4 != "0")))
                {
                    return false;
                }
            }
            return true;
        }
        #endregion


        #region 前期节点和 成功上传 暂时不用了
        public static async Task<(IEnumerable<dynamic>, int)> GetXMQQIndexList(CProManageQQQuerry querry, string strdepids)
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.Jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.Jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.Xmmc)))
            {
                str = str + " and xmmc like '%" + querry.Xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.Xmfl)))
            {
                str = str + " and xmflname = '" + querry.Xmfl + "'";
            }
            if (!(string.IsNullOrEmpty(querry.Zjly)))
            {
                str = str + " and zjly = '" + querry.Zjly + "'";
            }
            if (!(string.IsNullOrEmpty(querry.year)))
            {
                str = str + " and xmnf = '" + querry.year + "'";
            }
            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }

            if (!(string.IsNullOrEmpty(querry.sftb)))
            {
                str = str + " and qqzt = '" + querry.sftb + "'";
            }


            string strsql = "select * from View_ProQQLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }


        public static async Task<bool> GetExportTreeNodeFromBase(string strxmid, string strlx)
        {
            string strsql = @"with aa as
(
select *, 1 lev from CProXMQQ where xmid = '0' and lx = '" + strlx + @"' and pid = '0'
union all
select t.*, aa.lev + 1000  from CProXMQQ t inner join aa on t.pid = aa.id and t.xmid = '0'
)
select * from aa order by lev+arr asc ";

            List<CProXMQQ> table = (List<CProXMQQ>)await new RepositoryFactory().BaseRepository().FindList<CProXMQQ>(strsql, null);


            Dictionary<string, string> dics = new Dictionary<string, string>();
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                for (int i = 0; i < table.Count; i++)
                {
                    string orgids = table[i].id;
                    string newids = CommonHelper.GetUniqueId().ToString();
                    if (!dics.ContainsKey(orgids))
                    {
                        dics.Add(orgids, newids);
                    }
                }

                for (int i = 0; i < table.Count; i++)
                {
                    string orgids = table[i].id;
                    string newids = dics[orgids];

                    table[i].id = newids;
                    table[i].xmid = strxmid;
                    table[i].bm = await Assets.CreateZCDM(irp, "XM", "XMJD");
                    if (table[i].pid.Trim() != "0")
                        table[i].pid = dics[table[i].pid];

                    //foreach (var item in dics)
                    //{
                    //    table[i].pid = table[i].pid.Replace(item.Key, item.Value);
                    //}

                    await irp.Insert(table[i]);
                }
                return true;

            });

        }

        //查询树节点 lx = ''
        public static async Task<List<dynamic>> GetXMTreeNodeByXmid(string strxmid, string strlx)
        {
            //string strsql = "select * from CProXMQQ where xmid = '" + strxmid + "' and lx = '" + lx + "'";

            string strsql = @"select id, name,xmid, arr, main, startdateyj, enddateyj, datediff(DAY,startdateyj,enddateyj)hs,
 mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj,datediff(DAY, sjkssj, sjjssj)sjhs, sjbz, lx from CProXMQQ where xmid = '" + strxmid + "' and lx = '" + strlx + "' and pid = '0' order by convert(int,arr)";

            //           if (string.IsNullOrEmpty(lx))
            //           {
            //               strsql = @"select id, name,xmid, arr, main, startdateyj, enddateyj, datediff(DAY,startdateyj,enddateyj)hs,
            //mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj,datediff(DAY, sjkssj, sjjssj)sjhs, sjbz, lx from CProXMQQ where xmid = '" + strxmid + "' and pid = '0' order by convert(int,arr)";
            //           }

            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(strsql, null);

            List<dynamic> list = new List<dynamic>();
            //var table = await new RepositoryFactory().BaseRepository().FindList(sql, null);
            for (int num = 0; num < table.Rows.Count; num++)
            {
                //id, name, xmid, arr, main, startdateyj, enddateyj, hs, mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj, sjbz, lx
                dynamic d = new ExpandoObject();
                string strid = table.Rows[num]["id"].ToString();
                d.id = strid;
                d.pid = table.Rows[num]["pid"].ToString();

                d.name = table.Rows[num]["name"].ToString();
                d.bm = table.Rows[num]["bm"].ToString();
                d.xmid = table.Rows[num]["xmid"].ToString();

                d.startdateyj = table.Rows[num]["startdateyj"].ToDate();
                d.enddateyj = table.Rows[num]["enddateyj"].ToDate();
                d.hs = table.Rows[num]["hs"].ToInt();
                d.bz = table.Rows[num]["bz"].ToString();

                d.sjkssj = table.Rows[num]["sjkssj"].ToDate();
                d.sjjssj = table.Rows[num]["sjjssj"].ToDate();
                d.sjhs = table.Rows[num]["sjhs"].ToInt();
                d.sjbz = table.Rows[num]["sjbz"].ToString();

                d.zrr = table.Rows[num]["zrr"].ToString();
                d.zrrlxfs = table.Rows[num]["zrrlxfs"].ToString();
                d.bm = table.Rows[num]["bm"].ToString();


                d.lev = "1";

                await GetNodebyPid(d, strxmid, strid, 1);

                list.Add(d);
            }

            return list;
        }

        //查询树节点 name = ''
        public static async Task<List<dynamic>> GetXMQQTreeByXmid(string strxmid, string strnamelike)
        {
            //string strsql = "select * from CProXMQQ where xmid = '" + strxmid + "' and lx = '" + lx + "'";

            string strsql = @"select id, name,xmid, arr, main, startdateyj, enddateyj, datediff(DAY,startdateyj,enddateyj)hs,
 mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj,datediff(DAY, sjkssj, sjjssj)sjhs, sjbz, lx from CProXMQQ where xmid = '" + strxmid + "' and name = '" + strnamelike + "' and pid = '0' order by convert(int,arr)";

            if (string.IsNullOrEmpty(strnamelike))
            {
                strsql = @"select id, name,xmid, arr, main, startdateyj, enddateyj, datediff(DAY,startdateyj,enddateyj)hs,
 mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj,datediff(DAY, sjkssj, sjjssj)sjhs, sjbz, lx from CProXMQQ where xmid = '" + strxmid + "' and pid = '0' order by convert(int,arr)";
            }

            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(strsql, null);

            List<dynamic> list = new List<dynamic>();
            //var table = await new RepositoryFactory().BaseRepository().FindList(sql, null);
            for (int num = 0; num < table.Rows.Count; num++)
            {
                //id, name, xmid, arr, main, startdateyj, enddateyj, hs, mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj, sjbz, lx
                dynamic d = new ExpandoObject();
                string strid = table.Rows[num]["id"].ToString();
                d.id = strid;
                d.pid = table.Rows[num]["pid"].ToString();

                d.name = table.Rows[num]["name"].ToString();
                d.bm = table.Rows[num]["bm"].ToString();
                d.xmid = table.Rows[num]["xmid"].ToString();

                d.startdateyj = table.Rows[num]["startdateyj"].ToDate();
                d.enddateyj = table.Rows[num]["enddateyj"].ToDate();
                d.hs = table.Rows[num]["hs"].ToInt();
                d.bz = table.Rows[num]["bz"].ToString();

                d.sjkssj = table.Rows[num]["sjkssj"].ToDate();
                d.sjjssj = table.Rows[num]["sjjssj"].ToDate();
                d.sjhs = table.Rows[num]["sjhs"].ToInt();
                d.sjbz = table.Rows[num]["sjbz"].ToString();

                d.zrr = table.Rows[num]["zrr"].ToString();
                d.zrrlxfs = table.Rows[num]["zrrlxfs"].ToString();
                d.bm = table.Rows[num]["bm"].ToString();


                d.lev = "1";

                await GetNodebyPid(d, strxmid, strid, 1);

                list.Add(d);
            }

            return list;
        }

        //加载子节点
        public static async Task GetNodebyPid(dynamic nodes, string strxmid, string pid, int lev)
        {
            string strsql = @"select id, name,xmid, arr, main, startdateyj, enddateyj, datediff(DAY,startdateyj,enddateyj)hs,
 mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj,datediff(DAY, sjkssj, sjjssj)sjhs, sjbz, lx from CProXMQQ where xmid = '" + strxmid + "' and pid = '" + pid + "' order by convert(int,arr)";
            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(strsql, null);

            lev++;

            //var table = await new RepositoryFactory().BaseRepository().FindList(sql, null);
            List<dynamic> list = new List<dynamic>();
            for (int num = 0; num < table.Rows.Count; num++)
            {
                //id, name, xmid, arr, main, startdateyj, enddateyj, hs, mark, pid, bm, bz, zrr, zrrlxfs, sjkssj, sjjssj, sjbz, lx
                dynamic d = new ExpandoObject();
                string ids = table.Rows[num]["id"].ToString();
                d.id = ids;
                d.pid = table.Rows[num]["pid"].ToString();

                d.name = table.Rows[num]["name"].ToString();
                d.bm = table.Rows[num]["bm"].ToString();
                d.xmid = table.Rows[num]["xmid"].ToString();

                d.startdateyj = table.Rows[num]["startdateyj"].ToDate();
                d.enddateyj = table.Rows[num]["enddateyj"].ToDate();
                d.hs = table.Rows[num]["hs"].ToInt();
                d.bz = table.Rows[num]["bz"].ToString();

                d.sjkssj = table.Rows[num]["sjkssj"].ToDate();
                d.sjjssj = table.Rows[num]["sjjssj"].ToDate();
                d.sjhs = table.Rows[num]["sjhs"].ToInt();
                d.sjbz = table.Rows[num]["sjbz"].ToString();

                d.zrr = table.Rows[num]["zrr"].ToString();
                d.zrrlxfs = table.Rows[num]["zrrlxfs"].ToString();
                d.bm = table.Rows[num]["bm"].ToString();

                d.lev = lev;

                await GetNodebyPid(d, strxmid, ids, lev);
                list.Add(d);

            }

            nodes.childs = list;
        }

        //根据xmid 和类型查询子节点 sjc
        public static async Task<DataTable> GetXMQQAllByXmid(string strxmid, string strlx, string strid)
        {
            string strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0'  and xmid = '" + strxmid + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"' order by convert(int, qq.arr) asc
)

 select rr, id, name,bm, xmid, lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
 isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
 isnull(DATEDIFF(day, sjkssj,sjjssj )+1, 0)sjhs,
sjbz,lx
,
(
case when startdateyj IS null and enddateyj is null then '1'
when sjkssj IS null and sjjssj is null then '2'
else '3' end
)status
from xmqq order by rr asc";

            if (!string.IsNullOrEmpty(strlx))
            {
                strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0'  and xmid = '" + strxmid + @"' and lx = '" + strlx + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"'  order by convert(int, qq.arr) asc
)

 select rr, id, name,bm, xmid, lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
 isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
 isnull(DATEDIFF(day, sjkssj,sjjssj )+1, 0)sjhs,
sjbz,lx
,
(
case when startdateyj IS null and enddateyj is null then '1'
when sjkssj IS null and sjjssj is null then '2'
else '3' end
)status
from xmqq order by rr asc";

            }

            if (!string.IsNullOrEmpty(strid) && strid != "0")
            {
                strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where id = '" + strid + "'  and xmid = '" + strxmid + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"' order by convert(int, qq.arr) asc
)

 select rr, id, name,bm, xmid, lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
 isnull(DATEDIFF(day,startdateyj ,  enddateyj), 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
 isnull(DATEDIFF(day, sjkssj,sjjssj ), 0)sjhs,
sjbz,lx
,
(
case when startdateyj IS null and enddateyj is null then '1'
when sjkssj IS null and sjjssj is null then '2'
else '3' end
)status
from xmqq order by rr asc";
            }
            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);

            return dts;
        }

        //根据xmid 和类型查询子节点 sjc
        public static async Task<DataTable> GetXMGanttNodeByXmid(string strxmid, string strlx)
        {
            string strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0'  and xmid = '" + strxmid + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"' order by convert(int, qq.arr) asc
)

 select rr, id, name,bm, xmid, lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
 isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
 isnull(DATEDIFF(day, sjkssj,sjjssj )+1, 0)sjhs,
sjbz,lx
,
(
case when startdateyj IS null and enddateyj is null then '1'
when sjkssj IS null and sjjssj is null then '2'
else '3' end
)status
from xmqq order by rr asc";

            if (!string.IsNullOrEmpty(strlx))
            {
                strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0'  and xmid = '" + strxmid + @"' and lx = '" + strlx + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"'  order by convert(int, qq.arr) asc
)

 select rr, id, name,bm, xmid, lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
 isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
 isnull(DATEDIFF(day, sjkssj,sjjssj )+1, 0)sjhs,
sjbz,lx
,
(
case when startdateyj IS null and enddateyj is null then '1'
when sjkssj IS null and sjjssj is null then '2'
else '3' end
)status
from xmqq order by rr asc";

            }

          
            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);

            return dts;
        }

        //根据id查询子节点 sjc
        public static async Task<DataTable> GetXMQQByXmidAndID(string strxmid, string strid)
        {
            string strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where id = '" + strid + "'  and xmid = '" + strxmid + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"' order by convert(int, qq.arr) asc
)

select rr, id, name,bm, xmid, lev, pid,arr, startdateyj, enddateyj, 
isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,sjkssj,sjjssj,
isnull(DATEDIFF(day,sjkssj , sjjssj)+1, 0)sjhs,
sjbz,lx,(select name from CProXMQQ where id = xmqq.pid)pname from xmqq order by rr asc";

            if (string.IsNullOrEmpty(strid))
            {
                strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0' and xmid = '" + strxmid + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"' order by convert(int, qq.arr) asc
)

select rr, id, name,bm, xmid, lev, pid,arr, 
startdateyj, enddateyj, 
isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,sjkssj,sjjssj,
isnull(DATEDIFF(day,sjkssj ,  sjjssj)+1, 0)sjhs,
sjbz,lx,(select name from CProXMQQ where id = xmqq.pid)pname
from xmqq order by rr asc";

            }

            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);

            return dts;
        }

        //根据id查询本身节点 sjc
        public static async Task<DataTable> GetXMQQSigByXmidAndID(string strxmid, string strid)
        {
            string strsql = @"select  id, name,bm, xmid,1 lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
isnull(DATEDIFF(day, sjkssj,  sjjssj)+1, 0)sjhs,
sjbz,lx from CProXMQQ  where id = '" + strid + "' and xmid = '" + strxmid + "'";


            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);

            return dts;
        }

        //根据id查询本身节点 sjc
        public static async Task<dynamic> GetXMQQSigByID(string strid)
        {
            string strsql = @"select  id, name,bm, xmid,1 lev, pid,arr, 
 CONVERT(varchar(100), startdateyj, 23)startdateyj, 
 CONVERT(varchar(100), enddateyj, 23)enddateyj, 
isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,
 CONVERT(varchar(100), sjkssj, 23)sjkssj,
 CONVERT(varchar(100), sjjssj, 23)sjjssj,
isnull(DATEDIFF(day, sjkssj,  sjjssj)+1, 0)sjhs,
sjbz,lx from CProXMQQ  where id = '" + strid + "' ";


            var dts = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            return dts;
        }


        //成果上传前期节点列表 返回datatable
        //Task<IEnumerable<dynamic>> sjc
        public static async Task<DataTable> GetXMQQByXmid(string strxmid)
        {
            string strsql = "select * from CProXMQQ where xmid = '" + strxmid + "'";

            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);
            if (dts.Rows.Count > 0)
            {
                strsql = @"with xmqq as
(
select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0' and xmid = '" + strxmid + @"' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '" + strxmid + @"' order by convert(int, qq.arr) asc
)

select rr, id, name,bm, xmid, lev, pid,arr, startdateyj, enddateyj, 
isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,sjkssj,sjjssj,
isnull(DATEDIFF(day,sjkssj ,  sjjssj)+1, 0)sjhs,
sjbz,lx from xmqq order by rr asc";

                dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);
            }
            else
            {
                strsql = @"with xmqq as
                (
                select top 100 percent
 CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by pid order by arr)) rr, *, 1 lev from CProXMQQ
where pid = '0' and xmid = '0' order by convert(int, arr) asc
 union all
 select top 100 percent CONVERT(nvarchar(max), xmqq.rr) + '.' +
  RIGHT('0000' + CONVERT(nvarchar(max), ROW_NUMBER() over(PARTITION by qq.pid order by convert(int, qq.arr) asc)), 2)
, qq.*, xmqq.lev + 1  from CProXMQQ qq inner
                      join xmqq
  on qq.pid = xmqq.id where qq.xmid = '0' order by convert(int, qq.arr) asc
)

select rr, id, name, bm, xmid, lev, pid,arr, startdateyj, enddateyj, 
isnull(DATEDIFF(day,startdateyj ,  enddateyj)+1, 0)jhhs
,bz,zrr,zrrlxfs,sjkssj,sjjssj,
isnull(DATEDIFF(day,sjkssj ,  sjjssj)+1, 0)sjhs,
sjbz,lx from xmqq order by rr asc";

                dts = await new RepositoryFactory().BaseRepository().FindTable(strsql);


                Dictionary<string, string> dic = new Dictionary<string, string>();
                for (int i = 0; i < dts.Rows.Count; i++)
                {
                    string id = dts.Rows[i]["id"].ToString();
                    if (!dic.ContainsKey(id))
                        dic.Add(id, CommonHelper.GetUniqueId().ToString());

                }
                //添加一个pid = 0 的可能性
                if (!dic.ContainsKey("0"))
                    dic.Add("0", "0");

                for (int i = 0; i < dts.Rows.Count; i++)
                {
                    dts.Rows[i]["id"] = dic[dts.Rows[i]["id"].ToString()].ToString();
                    dts.Rows[i]["pid"] = dic[dts.Rows[i]["pid"].ToString()].ToString();
                    dts.Rows[i]["xmid"] = strxmid;
                }


                //新增到库里
                await TranstionRule.GetRunActionTransAsync(async irp =>
                {
                    //先删除xmid 下面的所有节点，再新增进来
                    await irp.DeleteAny<CProXMQQ>(new { xmid = strxmid });

                    //新增进来
                    for (int i = 0; i < dts.Rows.Count; i++)
                    {
                        CProXMQQ xmqq = new CProXMQQ();
                        xmqq.id = dts.Rows[i]["id"].ToString();
                        xmqq.pid = dts.Rows[i]["pid"].ToString();
                        xmqq.arr = CommonHelper.ParseInt(dts.Rows[i]["arr"].ToString());
                        xmqq.xmid = dts.Rows[i]["xmid"].ToString();
                        xmqq.name = dts.Rows[i]["name"].ToString();
                        xmqq.lx = dts.Rows[i]["lx"].ToString();

                        await irp.Insert(xmqq);

                    }

                    return true;

                });



            }

            return dts;
        }

        public async static Task<int> AUDXMQQ(List<CProXMQQ> xmqqs, string ope)
        {
            if (ope == "add")
            {
                await TranstionRule.GetRunActionTransAsync(async irp =>
                {


                    int pidindex = 0;
                    int index = 0;
                    for (int i = 0; i < xmqqs.Count; i++)
                    {
                        //string strsql = "select ISNULL(MAX(arr),0)+1 arr from CProXMQQ where xmid = '"+ xmqqs[i].xmid + "' and pid = '0'";
                        //irp.FindEntity();

                        if (xmqqs[i].pid == "0")
                        {
                            pidindex = pidindex + 1;
                            xmqqs[i].arr = pidindex;
                            index = 0;
                        }
                        else
                        {
                            index = index + 1;
                            xmqqs[i].arr = index;
                        }
                        //更新排序
                        await irp.Insert(xmqqs[i]);
                    }
                    return true;

                });

                return 1;
            }
            return 1;
        }


        /// <summary>
        /// 项目前期
        /// </summary>
        /// <param name="xmqq"></param>
        /// <param name="ope"></param>
        /// <returns></returns>
        public async static Task<int> AUDXMQQ(CProXMQQ xmqq, string ope)
        {
            if (ope == "add")
            {
                await TranstionRule.GetRunActionTransAsync(async irp =>
                {
                    if (xmqq.arr != 0)
                    {
                        string strsqlUPaRR = @"update CProXMQQ
set arr = tt.rrr from CProXMQQ,(
select(case when rr < " + xmqq.arr + @" then rr else rr + 1 end)rrr,*from(
   select ROW_NUMBER() over(order by CONVERT(int, arr) asc)rr
    , *from CProXMQQ where pid = '" + xmqq.pid + "' and xmid ='" + xmqq.xmid + @"'
    )t
 )tt where CProXMQQ.id = tt.id and CProXMQQ.xmid = '" + xmqq.xmid + "'";

                        //更新排序
                        await irp.ExecuteSql(strsqlUPaRR);

                        await irp.Insert(xmqq);

                        strsqlUPaRR = @" update CProXMQQ
set arr = tt.rrr from CProXMQQ,(
select rr rrr,*from(
   select ROW_NUMBER() over(order by CONVERT(int, arr) asc)rr
    , *from CProXMQQ where pid = '" + xmqq.pid + "' and xmid ='" + xmqq.xmid + @"'
    )t
 )tt where CProXMQQ.id = tt.id and CProXMQQ.xmid = '" + xmqq.xmid + "'";

                        //更新排序
                        await irp.ExecuteSql(strsqlUPaRR);
                    }
                    else
                    {
                        await irp.Insert(xmqq);
                        string strsqlUPaRR = @" update CProXMQQ
 set arr = (
 select ISNULL(MAX(rr),1)rr from (
    select ROW_NUMBER() over(order by CONVERT(int, arr) asc)rr
    , *from CProXMQQ where pid = '" + xmqq.pid + "' and xmid ='" + xmqq.xmid + @"'
    )t)
    where id = '" + xmqq.id + "'";

                        //更新排序
                        await irp.ExecuteSql(strsqlUPaRR);
                    }



                    return true;

                });

                return 1;

            }
            else if (ope == "edit")
            {
                await TranstionRule.GetRunActionTransAsync(async irp =>
                {
                    if (null == xmqq.arr)
                    {
                        await irp.Update(xmqq);
                    }
                    else
                    {
                        string strsqlUPaRR = @"update CProXMQQ
set arr = tt.rrr from CProXMQQ,(
select(case when rr < " + xmqq.arr + @" then rr else rr + 1 end)rrr,*from(
   select ROW_NUMBER() over(order by CONVERT(int, arr) asc)rr
    , *from CProXMQQ where pid = '" + xmqq.pid + "' and xmid ='" + xmqq.xmid + @"'
    )t
 )tt where CProXMQQ.id = tt.id and CProXMQQ.xmid = '" + xmqq.xmid + "'";


                        //更新排序
                        await irp.ExecuteSql(strsqlUPaRR);

                        await irp.Update(xmqq);

                        strsqlUPaRR = @" update CProXMQQ
set arr = tt.rrr from CProXMQQ,(
select rr rrr,*from(
   select ROW_NUMBER() over(order by CONVERT(int, arr) asc)rr
    , *from CProXMQQ where pid = '" + xmqq.pid + "' and xmid ='" + xmqq.xmid + @"'
    )t
 )tt where CProXMQQ.id = tt.id and CProXMQQ.xmid = '" + xmqq.xmid + "'";

                        //更新排序
                        await irp.ExecuteSql(strsqlUPaRR);
                    }
                    return true;
                });

                return 1;


            }
            else if (ope == "del")
            {
                return await new RepositoryFactory().BaseRepository().DeleteAny<CProXMQQ>(new { id = xmqq.id });
            }
            else
            {
                return 0;
            }

        }

        //删除前期节点
        public static async Task DeleteCProXMQQMulti(Root ids)
        {

            await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                for (int i = 0; i < ids.ids.Count; i++)
                {
                    CProXMQQ qq = await irp.FindEntityByKey<CProXMQQ>(ids.ids[i].id);

                    if (null != qq)
                    {

                        string delsql = "delete from CProXMQQ where id = '" + ids.ids[i].id + "'";
                        await irp.ExecuteSql(delsql);

                        //删除所有子节点
                        delsql = "delete from CProXMQQ where pid = '" + ids.ids[i].id + "'";
                        await irp.ExecuteSql(delsql);

                        string strsql = @"update CProXMQQ
set arr = tt.rr from CProXMQQ,(
select ROW_NUMBER() over(order by CONVERT(int, arr) asc)rr
 , * from CProXMQQ where pid = '" + qq.pid + "' and xmid ='" + qq.xmid + @"'
 )tt where CProXMQQ.id = tt.id";
                        await irp.ExecuteSql(delsql);
                    }

                }





                return true;
            });

        }



        ////甘特图节点列表
        //public static async Task<IEnumerable<dynamic>> GetGanttByXmid(string xmid)
        //{
        //    string sql = "select CProXMQQ.* ,scsj ,isnull(DATEDIFF(day,scsj,startdateyj),0) sjhs from CProXMQQ left join dbo.CProYChengGuo ON CProXMQQ.id = CProYChengGuo.jdid where CProXMQQ.xmid = '" + xmid + "' and mark = '1'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}
        ////甘特图勾选列表
        //public static async Task<IEnumerable<dynamic>> GetSelectGanttByXmid(string xmid)
        //{
        //    string sql = "select * from CProXMQQ where xmid = '" + xmid + "' and main = '1'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}

        ////更新甘特图节点
        //public static async Task MarkCProXMQQ(Root ids)
        //{
        //    string sxmid = "";
        //    foreach (BaseDeleteMulti idm in ids.ids)
        //    {
        //        string xmidsql = "select xmid from CProXMQQ where id = '" + idm.id + "'";

        //        DataTable table = await new RepositoryFactory().BaseRepository().FindTable(xmidsql, null);
        //        if (table.Rows.Count>0)
        //        {
        //            sxmid = table.Rows[0]["xmid"].ToString();
        //        }
        //    }
        //    string updsql = "update CProXMQQ set mark = '0' where xmid = '" + sxmid + "'";

        //    await new RepositoryFactory().BaseRepository().ExecuteSql(updsql);

        //    foreach (BaseDeleteMulti idm in ids.ids)
        //    {
        //        string updsql2 = "update CProXMQQ set mark = '1' where id = '" + idm.id + "'";

        //        await new RepositoryFactory().BaseRepository().ExecuteSql(updsql2);
        //    }

        //}
        ////新增修改前期节点
        //public static async Task InsertCProXMQQ(CProXMQQ xmqq)
        //{
        //    CProXMQQ count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProXMQQ>(xmqq.id);
        //    if (count is null)
        //    {

        //        string strid = CommonHelper.GetUniqueId().ToString();
        //        xmqq.id = strid;
        //        xmqq.mark = "0";
        //        await new RepositoryFactory().BaseRepository().Insert(xmqq);
        //    }
        //    else
        //    {
        //        xmqq.mark = "0";
        //        await new RepositoryFactory().BaseRepository().Update(xmqq);
        //    }
        //}
        ////删除前期节点
        //public static async Task DeleteCProXMQQMulti(Root ids)
        //{
        //    foreach (BaseDeleteMulti idm in ids.ids)
        //    {
        //        string sql = "select * from CProYChengGuo where jdid = '" + idm.id + "'";
        //        DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        //        if(table.Rows.Count>0)
        //        { 
        //            throw new Exception("该节点已有成果，不能删除");
        //        }
        //    }
        //    foreach (BaseDeleteMulti idm in ids.ids)
        //    {
        //        string delsql = "delete from CProXMQQ where id = '" + idm.id + "'";

        //        await new RepositoryFactory().BaseRepository().ExecuteSql(delsql);
        //    }
        //}
        ////新增修改成果上传
        //public static async Task InsertCProYChengGuo(CProYChengGuo cg, string uid)
        //{
        //    CProYChengGuo count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProYChengGuo>(cg.id);
        //    if (count is null)
        //    {

        //        string strid = CommonHelper.GetUniqueId().ToString();
        //        cg.id = strid;
        //        cg.createuser = uid;
        //        cg.status = "0";
        //        await new RepositoryFactory().BaseRepository().Insert(cg);
        //    }
        //    else
        //    {
        //        cg.status = "0";
        //        await new RepositoryFactory().BaseRepository().Update(cg);
        //    }
        //}
        #endregion


        #region 计划开工

        //获取项目计划开工列表
        public static async Task<(IEnumerable<dynamic>, int)> GetPlancommenindexList(CProPlancommenQuerry querry, string strdepids)
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmbm)))
            {
                str = str + " and xmbm = '" + querry.xmbm + "'";
            }
            if (!(string.IsNullOrEmpty(querry.xmnf)))
            {
                str = str + " and xmnf = '" + querry.xmnf + "'";
            }
 
            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }

            str += " and xmid not in (select xmid from CProGD)";

            string strsql = "select * from View_ProJHKGLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            if (querry.size == 0)
            {
                querry.size = 20;
            }
            if (querry.current == 0)
            {
                querry.current = 1;
            }
             

            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public static async Task<dynamic> GetPlancommenByid(string id)
        {
            string strsql = "select * from View_ProJHKGLists where id = '" + id + "'";
            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);
        }

        public static async Task<IEnumerable<dynamic>> GetPlancommenByxmid(string xmid)
        {
            string strsql = "select * from View_ProJHKGLists where xmid = '" + xmid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        public async static Task<bool> InsertCProPlancommen(CProManageJHKG cpro,string ope)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                if (ope == "add")
                {
                    await new RepositoryFactory().BaseRepository().Insert(cpro);
                }
                else
                {
                    await new RepositoryFactory().BaseRepository().Update(cpro);
                }
                return true;
            });
        }

        public async static Task<bool> DeletePlancommenMulti(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    //string strsql = "delete from ";
                    // jhkg = new CProManageJHKG();
                    //jhkg.id = idm.id;
                    await irp.DeleteAny<CProManageJHKG>(new { id = idm.id });
                }

                return true;

            });
        }


        #endregion

        #region 预算

        //项目首页报表
        public static async Task<(IEnumerable<dynamic>, int)> GetXZXMList(CProManageQuerry querry, string strdepids)
        {
            string str = " and spstatus = '"+ querry.status + "'";

            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmfl)))
            {
                str = str + " and xmflname = '" + querry.xmfl + "'";
            }
            if (!(string.IsNullOrEmpty(querry.zjly)))
            {
                str = str + " and zjly = '" + querry.zjly + "'";
            }
            //if (!(string.IsNullOrEmpty(querry.year)))
            //{
            //    str = str + " and xmnf = '" + querry.year + "'";
            //}
            //if (!(string.IsNullOrEmpty(querry.status)))
            //{
            //    str = str + " and spstatus = '" + querry.status + "'";
            //}

            str += " and id not in (select xmid from CProGD)";

            //预算
            if (querry.type == "ys")
            {
                if (string.IsNullOrEmpty(querry.year))
                {
                    querry.year = DateTime.Now.Year.ToString();
                }

                str += " and id not in (select xmid from CProManCZBK where nd = '"+ querry.year + "')";
            
            }
            else if (querry.type == "jhkg")
            {
                //计划开工
                str += " and id not in (select xmid from CProManageJHKG)";
            }
           

            string strsql = "select * from View_ProLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }


        //获取项目计划开工列表
        public static async Task<(IEnumerable<dynamic>, int)> GetYSList(CProYSQuerry querry, string strdepids)
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmbm)))
            {
                str = str + " and xmbm = '" + querry.xmbm + "'";
            }
            if (!(string.IsNullOrEmpty(querry.xmnf)))
            {
                str = str + " and xmnf = '" + querry.xmnf + "'";
            }

            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }

            str += " and xmid not in (select xmid from CProGD)";

            string strsql = "select * from View_ProysLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            if (querry.size == 0)
            {
                querry.size = 20;
            }
            if (querry.current == 0)
            {
                querry.current = 1;
            }


            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public static async Task<dynamic> GetYSByid(string id)
        {
            string strsql = "select * from View_ProysLists where id = '" + id + "'";
            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);
        }

        public static async Task<IEnumerable<dynamic>> GetYSByxmid(string xmid)
        {
            string strsql = "select * from View_ProysLists where xmid = '" + xmid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        public async static Task<bool> InsertCProYS(CProManCZBK cpro, string ope)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                if (ope == "add")
                {
                    await new RepositoryFactory().BaseRepository().Insert(cpro);
                }
                else
                {
                    await new RepositoryFactory().BaseRepository().Update(cpro);
                }
                return true;
            });
        }

        public async static Task<bool> DeleteYSMulti(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await irp.DeleteAny<CProManCZBK>(new { id = idm.id });
                }

                return true;

            });
        }

        #endregion


        #region 预算变更

        //项目首页报表
        public static async Task<(IEnumerable<dynamic>, int)> GetXZYSList(CProXZYSQuerry querry, string strdepids)
        {
            string str = " and spstatus = '" + querry.status + "'";

            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmbm)))
            {
                str = str + " and xmflname = '" + querry.xmbm + "'";
            }
            if (!(string.IsNullOrEmpty(querry.xmfl)))
            {
                str = str + " and xmflname = '" + querry.xmfl + "'";
            }
            //if (!(string.IsNullOrEmpty(querry.zjly)))
            //{
            //    str = str + " and zjly = '" + querry.zjly + "'";
            //}
            if (!(string.IsNullOrEmpty(querry.ysnd)))
            {
                str = str + " and nd = '" + querry.ysnd + "'";
            }
            //if (!(string.IsNullOrEmpty(querry.status)))
            //{
            //    str = str + " and spstatus = '" + querry.status + "'";
            //}

            str += " and xmid not in (select xmid from CProGD)";

            //View_ProysLists
            string strsql = "select * from View_ProysLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }


        //获取项目计划开工列表
        public static async Task<(IEnumerable<dynamic>, int)> GetYSBGList(CProYSBGQuerry querry, string strdepids)
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmbm)))
            {
                str = str + " and xmbm = '" + querry.xmbm + "'";
            }
            if (!(string.IsNullOrEmpty(querry.ysnd)))
            {
                str = str + " and xmnf = '" + querry.ysnd + "'";
            }

            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }

            str += " and xmid not in (select xmid from CProGD)";

            string strsql = "select * from View_ProysbgLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            if (querry.size == 0)
            {
                querry.size = 20;
            }
            if (querry.current == 0)
            {
                querry.current = 1;
            }


            return await new RepositoryFactory().BaseRepository().FindList(strsql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public static async Task<dynamic> GetYSBGByid(string bgid)
        {
            string strsql = "select * from View_ProysbgLists where id = '" + bgid + "'";
            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);
        }

        public static async Task<IEnumerable<dynamic>> GetYSBGByysid(string ysid)
        {
            string strsql = "select * from View_ProysbgLists where ysid = '" + ysid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        public async static Task<bool> InsertCProYSbg(CProManCZBKbg cpro, string ope)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                if (ope == "add")
                {
                    await new RepositoryFactory().BaseRepository().Insert(cpro);
                }
                else
                {
                    await new RepositoryFactory().BaseRepository().Update(cpro);
                }
                return true;
            });
        }

        public async static Task<bool> DeleteYSbgMulti(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await irp.DeleteAny<CProManCZBKbg>(new { id = idm.id });
                }

                return true;

            });
        }

        #endregion
    }
}
